class Statistics
  #een product wordt geselecteerd aan de hand van wat de gebruiker al heeft gekocht
  #stel de gebruiker heeft product A en B gekocht, dan wordt per product bekeken welke andere gebruikers product A en B kochten
  #van die gebruikers wordt bekeken welke andere producten zij nog meer kochten
  #stel voor product A is dat C en D
  #dan wordt het aantal producten dat gekocht is gegeven product A opgeteld -> $totaal
  #vervolgens wordt voor product C en D de totaal aan geteld gegeven A
  #en dan wordt dat gedeeld door $totaal
  #Dit is omdat er anders scheve vergelijkingen komen
  #Stel van product A zijn er 200 verkocht en gegeven product A zijn er 100 van B en 100 van C verkocht
  #Als er dan van product B 1000 verkocht zijn en gegeven product B, Product E en F allebij 200
  #Dan zouden zonder deling, producten E en F gekozen worden, terwijl het verband tussen A en C en D veel sterker is dan B, E en F
  def self.find_recommended_products_for_user(user,n)
    #selecteer alle producten van een bepaalde gebruiker
    products_from_user_query = ["SELECT products.id
    FROM products
    INNER JOIN order_items
      ON products.id = order_items.product_id
    INNER JOIN orders
      ON order_items.order_id = orders.id
    INNER JOIN users
      ON (orders.user_id = users.id) WHERE users.id = ? GROUP BY products.id",user.id.to_s]
    user_products = Product.find_by_sql(products_from_user_query)
    
    #conditions zijn de producten die niet geselecteerd moeten worden in de tabel
    conditions = ""
    conditions_variables = []
    if(user_products.size > 0)
      
      conditions_array = []
      conditions = "AND NOT products.id IN("
      
      user_products.each do |product|
        conditions_array <<= '?'
        conditions_variables <<= product.id.to_s
      end
      conditions += conditions_array.join(",")
      conditions +=")"
    end
    statistics = {}
    user_products.each do |product|
      #geeft het totaal aantal verkochte producten terug van gebruikers die een bepaald product hebben gekocht
      total_query = ["SELECT products.id, 
      SUM(order_items.quantity)
        AS total_quantity
      FROM products
      INNER JOIN order_items
        ON products.id = order_items.product_id
      INNER JOIN orders
        ON order_items.order_id = orders.id
      INNER JOIN users
        ON orders.user_id = users.id
      WHERE users.id =
        (SELECT users.id
        FROM users
        INNER JOIN orders
          ON users.id = orders.user_id
        INNER JOIN order_items
          ON orders.id = order_items.order_id
        INNER JOIN products
          ON order_items.product_id = products.id
        WHERE products.id = ?)",product.id.to_s]
      total_quantity = Product.find_by_sql(total_query)[0].total_quantity.to_f
      #de query hierboven geeft de producten terug die gebruikers hebben gekocht die een bepaald product hebben gekocht
      products_query = ["SELECT products.id,
      SUM(order_items.quantity)
        AS total_quantity
      FROM products
      INNER JOIN order_items
        ON products.id = order_items.product_id
      INNER JOIN orders
        ON order_items.order_id = orders.id
      INNER JOIN users
        ON orders.user_id = users.id
      WHERE users.id =
        (SELECT users.id
        FROM users
          INNER JOIN orders
            ON users.id = orders.user_id
          INNER JOIN order_items
            ON orders.id = order_items.order_id
          INNER JOIN products
            ON order_items.product_id = products.id
          WHERE products.id = ?) #{conditions} GROUP BY products.id ORDER BY total_quantity DESC",product.id.to_s]+conditions_variables
      products = Product.find_by_sql(products_query)
      if(total_quantity > 0)
        products.each do |product_total|
          if(statistics[product_total.id] == nil || statistics[product_total.id] < product_total.total_quantity.to_f/total_quantity)
            statistics[product_total.id] = product_total.total_quantity.to_f/total_quantity.to_f
          end
        end
      end
    end
    statistics = statistics.sort {|a,b| b[1]<=>a[1]}
    
    product_ids = []

    statistics[0..n].each do |i|
       product_ids <<= i[0]
    end

    Product.find(product_ids)
  end
  
  #geeft de beste verkochte producten binnen een categorie terug
  def self.find_recommended_products_for_category(category,n)
    date_now = Date.today+1
    date_last_month = date_now-30
    products_query = ["SELECT products.*, SUM(order_items.quantity) AS total_quantity FROM products LEFT JOIN order_items ON products.id = order_items.product_id INNER JOIN orders ON order_items.order_id = orders.id LEFT JOIN product_categories ON products.id = product_categories.product_id LEFT JOIN categories ON product_categories.category_id = categories.id WHERE orders.created_at >= ? AND orders.created_at <= ? AND categories.left >= ? AND categories.right <= ? GROUP BY products.id ORDER BY total_quantity DESC",date_last_month.to_s,date_now.to_s,category.left.to_s,category.right.to_s]
    return Product.find_by_sql(products_query)[0..n]
  end
  
  #selecteer producten gegeven een ander product
  #dat wordt gebaseerd op gebruikers die bv product A kochten en daarbij wordt gekeken welke andere producten onder die gebruikers populair zijn
  def self.find_recommended_products_for_product(product,n)
    products_query = ["SELECT products.*, SUM(order_items.quantity) AS total_quantity FROM products LEFT JOIN order_items ON products.id = order_items.product_id LEFT JOIN orders ON order_items.order_id = orders.id LEFT JOIN users ON orders.user_id = users.id WHERE users.id = (SELECT users.id FROM users LEFT JOIN orders ON users.id = orders.user_id LEFT JOIN order_items ON orders.id = order_items.order_id LEFT JOIN products ON order_items.product_id = products.id WHERE products.id = ?) AND products.id != ? GROUP BY products.id ORDER BY total_quantity DESC",product.id.to_s,product.id.to_s]
    return Product.find_by_sql(products_query)[0..n]
  end
  
  #selecteerd de best verkochte producten
  def self.find_recommended_products(n)
    date_now = Date.today+1
    date_last_month = date_now-30
    products_query = ["SELECT products.*, SUM(order_items.quantity) AS total_quantity FROM products LEFT JOIN order_items ON products.id = order_items.product_id LEFT JOIN orders ON order_items.order_id = orders.id WHERE orders.created_at >= ? AND orders.created_at <= ? GROUP BY products.id ORDER BY total_quantity DESC",date_last_month.to_s,date_now.to_s]
    return Product.find_by_sql(products_query)[0..n]
  end
  
  #sommeert voor producten de totaal aantal verkocht en de prijs gegeven een lijst met producten
  def self.get_summed_result(table)
    quantity = 0
    price = 0
    table.each do |item|
      if(item.id != nil)
        quantity += item.total_quantity.to_i
        price += item.total_price.to_i
      end
    end
    return {:quantity => quantity, :price => price}
  end
end